package com.example.tingsugar.mapper;

import com.example.tingsugar.bean.AlbumBean;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface AlbumMapper {
    
    @Select("SELECT\n" +
        "    #{date} AS date,\n" +
        "    multiIf(play.album_name != '', play.album_name, " +
        "            order.album_name != '', order.album_name," +
        "            subscribe.album_name != '', subscribe.album_name," +
        "            comment.album_name " +
        "           ) album_name,\n" +
        "    play_user_ct,\n" +
        "    play_ct,\n" +
        "    play_duration,\n" +
        "    order_user_ct,\n" +
        "    order_ct,\n" +
        "    order_amount,\n" +
        "    subscribe_user_ct,\n" +
        "    subscribe_ct,\n" +
        "    comment_user_ct\n" +
        "FROM\n" +
        "(\n" +
        "    SELECT\n" +
        "        album_name,\n" +
        "        sum(user_ct) AS play_user_ct,\n" +
        "        sum(play_ct) AS play_ct,\n" +
        "        sum(play_seconds) AS play_duration\n" +
        "    FROM dws_album_play_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY album_name\n" +
        ") AS play\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        album_name,\n" +
        "        sum(order_user_ct) AS order_user_ct,\n" +
        "        sum(order_ct) AS order_ct,\n" +
        "        sum(split_order_amount) AS order_amount\n" +
        "    FROM dws_album_order_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY album_name\n" +
        ") AS order ON play.album_name = order.album_name\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        album_name,\n" +
        "        sum(subscribe_user_ct) AS subscribe_user_ct,\n" +
        "        sum(subscribe_ct) AS subscribe_ct\n" +
        "    FROM dws_album_subscribe_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY album_name\n" +
        ") AS subscribe ON play.album_name = subscribe.album_name\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        album_name,\n" +
        "        sum(comment_user_ct) AS comment_user_ct\n" +
        "    FROM dws_album_comment_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY album_name\n" +
        ") AS comment ON play.album_name = comment.album_name")
    List<AlbumBean> album(Integer date);
    
}
